options compress=YES;

libname original ''; 
libname fra '';

* Create Table A3 ;

***********************************************************;
* APPEND ALL SEGMENTS ;
* *********************************************************;

data mbrphusser_all;
	set 
		fra.mbrphusser02
		fra.mbrphusser03
		fra.mbrphusser05
		fra.mbrphusser06
		fra.mbrphusser09
		fra.mbrphusser10
		fra.mbrphusser15
		fra.mbrphusser16
		fra.mbrphusser19
		fra.mbrphusser20
		;

run;

***********************************************************;
* COLLAPSE BY COHORT AND RETIREMENT/CLAIMING YEAR AND MERGE;
* *********************************************************;

* TWO SETS OF FILES: RESTRICT TO 10+ YEARS OF EARNINGS, THOSE WORKING AT 55;
data mbrphusser_earn10yr;
	set mbrphusser_all (where=(n_earnyrnum60>=10));
run;

data mbrphusser_earn55;
	set mbrphusser_all (where=(n_earnat55=1));
run;

%macro collapse(set);

* Create summary stats ;
data mbrphusser_&set._summstats ;
	set mbrphusser_&set. 
		(keep= ssn ser_dobyy n_rflow_: n_hzlast_: n_hzfirst_:
		ser_race ser_sex n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		) 
	;

	if 		ser_dobyy >= 1935 & ser_dobyy <= 1937 then coh = 1 ; 	
	else if ser_dobyy >= 1938 & ser_dobyy <= 1942 then coh = 2 ; 	
	else if ser_dobyy >= 1943 & ser_dobyy <= 1945 then coh = 3 ; 

	* Cohort characteristics ;
	if ser_sex="M" then n_male=1;
		else if ser_sex="F" then n_male=0;
		else n_male=.;

	%macro race(uc,lc);
	if ser_race="&uc." then n_race_&lc.=1;
		else n_race_&lc.=0;
	%mend race;
	%race(A,a);
	%race(B,b);
	%race(H,h);
	%race(W,w);
	if (ser_race="I" | ser_race="O" | ser_race="U") then n_race_o=1;
		else n_race_o=0;

run;

proc rank data=mbrphusser_&set._summstats out=mbrphusser_&set._summstats groups=2;
	var n_earnavg25to55;
	ranks n_earnavgmed;
run;

proc means noprint data=mbrphusser_&set._summstats nway;
	class coh ;
	var ser_dobyy 
		n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 
		n_retire_age: n_claim_age: n_diff: restat_num_rflow ;
	output out=fra.summstats_&set.
	N(ser_dobyy)=count 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		)=
	;
run;

proc means noprint data=mbrphusser_&set._summstats nway;
	class coh n_male ;
	var ser_dobyy 
		n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 
		n_retire_age: n_claim_age: n_diff: restat_num_rflow ;
	output out=fra.summstats_&set._bymale
	N(ser_dobyy)=count 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		)=
	;
run;

proc means noprint data=mbrphusser_&set._summstats nway;
	class coh n_earnavgmed ;
	var ser_dobyy 
		n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60 
		n_retire_age: n_claim_age: n_diff: restat_num_rflow ;
	output out=fra.summstats_&set._byearn
	N(ser_dobyy)=count 
	MEAN(n_male n_race_w n_race_h n_race_b n_race_a n_race_o 
		n_earnyrnum60 n_earnavg25to55 n_earndelt55to60
		n_retire_age: n_claim_age: n_diff: restat_num_rflow
		)=
	;
run;

* CONVERT DATA SETS TO STATA;
proc export data=fra.summstats_earn10yr_bymale
     file="&DIR\Table_A3_PanelA.dta"
     dbms=DTA REPLACE;
run;
proc export data=fra.summstats_earn10yr_byearn
     file="&DIR\Table_A3_PanelB.dta"
     dbms=DTA REPLACE;
run;

quit;



